...
PreparedStatement ps = connection.prepareStatement("/*+ cache */ select col from t where col2 = ?");
ps.setInt(1, 5);
ps.execute();
...
Teiid provides the capability to cache the results of specific user queries and virtual procedure calls. This caching technique can yield significant performance gains if users of the system submit the same queries or execute the same procedures often.
Caching of user query results including XML document model results.
Caching of virtual procedure results.
Scoping of results is automatically determined to be VDB/user (replicated) or session level.
Configurable number of cache entries and time to live.
Administrative clearing.
User query result set caching will cache result sets based on an exact match of the incoming SQL string and PreparedStatement parameter values if present. Caching only applies to SELECT, set query, and stored procedure execution statements; it does not apply to SELECT INTO statements, or INSERT, UPDATE, or DELETE statements.
End users or client applications explicitly state whether to use result set caching. This can be done by setting the JDBC ResultSetCacheMode execution property to true (default false) or by adding a Cache Hint to the query. Note that if either of these mechanisms are used, Teiid must also have result set caching enabled (the default is enabled).
The most basic form of the cache hint, /*+ cache */, is sufficient to inform the engine that the results of the non-update command should be cached.
...
PreparedStatement ps = connection.prepareStatement("/*+ cache */ select col from t where col2 = ?");
ps.setInt(1, 5);
ps.execute();
...
The results will be cached with the default ttl and use the SQL string and the parameter value as part of the cache key.
The pref_mem and ttl options of the cache hint may also be used for result set cache queries. If a cache hint is not specified, then the default time to live of the result set caching configuration will be used.
/*+ cache(pref_mem ttl:60000) */ select col from t
In this example the memory preference has been enabled and the time to live is set to 60000 milliseconds or 1 minute. The ttl for an entry is actually treated as it's maximum age and the entry may be purged sooner if the maximum number of cache entries has been reached.
Each query is re-checked for authorization using the current user’s permissions, regardless of whether or not the results have been cached.
Similar to materialized views, cached virtual procedure results are used automatically when a matching set of parameter values is detected for the same procedure execution. Usage of the cached results may be bypassed with an Hints and Options clause. See the OPTION NOCACHE section for more on its usage.
To indicate that a virtual procedure (only definable by Teiid Designer) should be cached, it's definition should include a Cache Hint.
/*+ cache */ CREATE VIRTUAL PROCEDURE BEGIN ... END
Results will be cached with the default ttl.
The pref_mem and ttl options of the cache hint may also be used for procedure caching.
Procedure results cache keys include the input parameter values. To prevent one procedure from filling the cache, at most 256 cache keys may be created per procedure per VDB.
A cached procedure will always produce all of its results prior to allowing those results to be consumed and placed in the cache. This differs from normal procedure execution which in some situations allows the returned results to be consumed in a streaming manner.
By default result set caching is enabled with 1024 maximum entries with a maximum entry age of 2 hours. There are actually 2 caches configured with these settings. One cache holds results that are specific to sessions and is local to each Teiid instance. The other cache holds VDB scoped results and can be replicated. See teiid subsystem configuration for tuning. The user may also override the default maximum entry age via the Cache Hint.
Result set caching is not limited to memory. There is no explicit limit on the size of the results that can be cached. Cached results are primarily stored in the BufferManager and are subject to it's configuration - including the restriction of maximum buffer space.
While the result data is not held in memory, cache keys - including parameter values - may be held in memory. Thus the cache should not be given an unlimited maximum size.
Result set cache entries can be invalidated by data change events. The max-staleness setting determines how long an entry will remain in the case after one of the tables that contributed to the results has been changed. See the Developer's Guide for further customization.
The result set cache can be cleared through the AdminAPI using the clearCache method. The expected cache key is "QUERY_SERVICE_RESULT_SET_CACHE".
connectAsAdmin()
clearCache("QUERY_SERVICE_RESULT_SET_CACHE")
...
See the Administrator's Guide for more on using the AdminAPI and AdminShell.
XML, BLOB, CLOB, and OBJECT type cannot be used as part of the cache key for prepared statement of procedure cache keys.
The exact SQL string, including the cache hint if present, must match the cached entry for the results to be reused. This allows cache usage to skip parsing and resolving for faster responses.
Result set caching is not transactional. Transactions depend on (and enforce) consistency of data, and cached data is not guaranteed to be consistent with the data store’s data.
Clearing the results cache clears all cache entries for all VDBs.